EXISTS Condition

This PostgreSQL tutorial explains how to use the PostgreSQL EXISTS condition with syntax and examples.

Description

The PostgreSQL EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in PostgreSQL is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

A SELECT statement that usually starts with SELECT * rather than a list of expressions or column names. To increase performance, you could replace the SELECT * with SELECT 1 since the column result of the subquery is not relevant (only the rows returned matters).

Note

• SQL statements that use the EXISTS condition in PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

Example - With SELECT Statement

Let's look at a simple example.

The following is a SELECT statement that uses the PostgreSQL EXISTS condition:

SELECT *

FROM products

WHERE EXISTS (SELECT 1

              FROM inventory

              WHERE products.product_id = inventory.product_id);

This PostgreSQL EXISTS condition example will return all records from the products table where there is at least one record in the inventory table with the matching product_id. We have used SELECT 1 in the subquery to increase performance since the column result set is not relevant to the EXISTS condition (only the existence of a returned row matters).

Example - With SELECT Statement using NOT EXISTS

The PostgreSQL EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *

FROM products

WHERE NOT EXISTS (SELECT 1

                  FROM inventory

                  WHERE products.product_id = inventory.product_id);

This PostgreSQL EXISTS example will return all records from the products table where there are no records in the inventory table for the given product_id.

Example - With INSERT Statement

The following is an example of an INSERT statement that uses the PostgreSQL EXISTS condition:

INSERT INTO contacts

(contact_id, contact_name)

SELECT supplier_id, supplier_name

FROM suppliers

WHERE EXISTS (SELECT 1

              FROM orders

              WHERE suppliers.supplier_id = orders.supplier_id);

Example - With UPDATE Statement

The following is an example of an UPDATE statement that uses the PostgreSQL EXISTS condition:

UPDATE suppliers

SET supplier_name = (SELECT customers.customer_name

                     FROM customers

                     WHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS (SELECT 1

              FROM customers

              WHERE customers.customer_id = suppliers.supplier_id);

Example - With DELETE Statement

The following is an example of a DELETE statement that uses the PostgreSQL EXISTS condition:

DELETE FROM contacts

WHERE EXISTS (SELECT 1

              FROM employees

              WHERE contacts.contact_id = employees.employee_id);